package cn.topevery.garden.dal.dao.impl;

import cn.topevery.garden.dal.dao.ClockInPeopleDao;
import cn.topevery.garden.entity.po.ClockInPeople;
import cn.topevery.garden.entity.query.ClockInPeopleQuery;
import cn.topevery.garden.entity.vo.ClockInPeopleVO;
import cn.topevery.garden.framework.base.BaseDaoImpl;
import hw.topevery.framework.db.CommandType;
import hw.topevery.framework.db.entity.DataPager;
import hw.topevery.framework.db.entity.SqlQueryMate;
import hw.topevery.framework.db.enums.ScriptConditionEnum;
import hw.topevery.framework.entity.DbCsp;
import hw.topevery.framework.entity.KeyValue;
import hw.topevery.framework.entity.Value;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author code-generate
 * @date 2022-01-12
 */
@Repository
public class ClockInPeopleDaoImpl extends BaseDaoImpl<ClockInPeople> implements ClockInPeopleDao {
    @Override
    public <P extends DbCsp> List<ClockInPeople> getSearch(P para) {
        return null;
    }


    @Override
    public DataPager<ClockInPeopleVO> listPage(ClockInPeopleQuery query) {
        String table = "t_clock_in_people tcip";

        String columns = "tcip.*";

        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns).setOrderBy("tcip.c_date desc,tcip.c_user_id_int desc");
        sqlQueryMate
                .where(true, "tcip.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(StringUtils.isNotBlank(query.beginDate),"tcip.c_work_date", ScriptConditionEnum.GreaterThanEq, query.beginDate)
                .where(StringUtils.isNotBlank(query.endDate), "tcip.c_work_date", ScriptConditionEnum.LessThanEq, query.endDate)
                .where(StringUtils.isNotBlank(query.getName()), "tcip.c_user_name", ScriptConditionEnum.Like, query.getName());
        return getDataPager(sqlQueryMate, pageSize, pageIndex, ClockInPeopleVO.class);
    }


    @Override
    public ClockInPeople getEntityByColumn(String key, String value) {
        KeyValue<String> map = new KeyValue<>();
        map.key = key;
        map.value = value;
        return getEntityByColumnEqValue(map);
    }

    /**
     * 新增每天的考勤记录
     *
     * @param
     * @return
     * @创建人 dong.zou
     * @创建时间 2022/1/12
     */
    @Override
    public Boolean insertClock() {
        Value<Boolean> res = new Value<>();
        String sql = "insert into t_clock_in_people\n" +
                "\t(c_user_name,c_user_id_int,c_user_id,c_extent_name,c_duty_name,c_date,c_work_date,c_type,c_status,c_db_create_date,c_db_status,c_valid_time,c_mileage)\n" +
                "\tselect tsr.c_obj_name,tsr.c_obj_id,p.c_user_id,tsr.c_extent_name,CONCAT(tdd.c_name,'(',date_format(tddt.c_begin,'%H:%i:%s'),'-' ,date_format(tddt.c_end,'%H:%i:%s'),')')  as c_duty_name,tsr.c_date,date_add(tsr.c_date, interval  tddt.c_begin  day_second)  as c_work_date,0 c_type,0 c_status, NOW() c_db_create_date,0 c_db_status,0 c_valid_time,0 c_mileage\n" +
                "from t_dingtalk_duty tdd " +
                "join t_dingtalk_duty_times  tddt on tdd.c_data_id=tddt.c_duty_data_id\n" +
                " join t_schedule_record tsr  on tsr.c_shift_id=tdd.c_id\n" +
                " join t_person p on p.c_id = tsr.c_obj_id\n" +
                " where tsr.c_db_status=0 and tsr.c_obj_type=1 and tsr.c_date=date_format(NOW(),'%Y-%m-%d') \n" +
                " and not exists (select 1 from t_clock_in_people where c_date = date_format(NOW(),'%Y-%m-%d') LIMIT 1);";
        run(sql, CommandType.Text, dbCmd -> {
            res.data = dbCmd.executeNonQuery();
        });
        return res.data;
    }

    /**
     * 自动更新每天的考勤记录
     *
     * @param
     * @return
     * @创建人 dong.zou
     * @创建时间 2022/1/12
     */
    @Override
    public Boolean UpDateInClock() {
        Value<Boolean> res = new Value<>();
        String sql = "update  t_clock_in_people a  join \n" +
                "t_curr_status_people b on a.c_user_id = b.c_user_id\n" +
                "set a.c_geo_x=b.c_geo_x,a.c_geo_y=b.c_geo_y,a.c_abs_x=b.c_abs_x,a.c_abs_y=b.c_abs_y,\n" +
                "a.c_type=1,a.c_status=1,a.c_gps_date=b.c_gps_date,a.c_address='范围内'\n" +
                "where a.c_date = date_format(NOW(),'%Y-%m-%d')\n" +
                "and a.c_type=0 and b.c_is_in_extent = 1\n" +
                "and b.c_gps_date >= DATE_ADD(a.c_work_date,INTERVAL -15 MINUTE) \n" +
                "and b.c_gps_date <= DATE_ADD(a.c_work_date,INTERVAL 15 MINUTE) ;";
        run(sql, CommandType.Text, dbCmd -> {
            res.data = dbCmd.executeNonQuery();
        });
        return res.data;
    }
    
    /**
     *  
     * @query 片区出勤统计
     * @return 
     * @创建人 dong.zou
     * @创建时间 2022/1/25
     */
    @Override
    public List<Map<String,Object>> getExtentWorkCountList(ClockInPeopleQuery query) {
        String table = "t_clock_in_people c\n";

        String columns = " c.c_extent_name,COUNT(1) c_count, SUM(CASE WHEN exists(select 1 from t_give_an_alarm g  where CAST(g.c_work_date AS DATE) = c.c_date and g.c_obj_type=0 and g.c_obj_id_int=c.c_user_id_int and g.c_status=2 LIMIT 1)  THEN 0 ELSE 1 END ) c_in_count";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true,"c.c_db_status",ScriptConditionEnum.Eq,0)
                .where(StringUtils.isNotBlank(query.beginDate),"c.c_date",ScriptConditionEnum.Eq,query.beginDate)
                .where(StringUtils.isNotBlank(query.getExtentName()),"c.c_extent_name",ScriptConditionEnum.Like,query.getExtentName()) ;
        sqlQueryMate.setGroupBy("c.c_extent_name");
        return getMap(sqlQueryMate);
    }

    /**
     *
     * @query 人员出勤统计
     * @return
     * @创建人 dong.zou
     * @创建时间 2022/1/25
     */
    @Override
    public List<Map<String,Object>> getPeopleWorkCountList(ClockInPeopleQuery query) {
        String table = " t_clock_in_people a\n" +
                "left join t_status_people_record b\n" +
                "on a.c_date = b.c_date \n" +
                "and a.c_user_id = b.c_user_id\n";

        String columns = " a.c_user_name,a.c_user_id,a.c_user_id_int,b.c_person_type,sum(distinct b.c_valid_time) c_valid_time,sum(distinct b.c_mileage) c_valid_mileage,\n" +
                "( select group_concat(distinct c.c_type order by c.c_id desc) from t_give_an_alarm c where CAST(c.c_work_date AS DATE) = a.c_date and c.c_obj_type=0 and c.c_obj_id_int=a.c_user_id_int and c.c_status=2 group by c.c_obj_id,c.c_obj_id_int) c_alarm_ids";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true,"a.c_db_status",ScriptConditionEnum.Eq,0)
                .where(StringUtils.isNotBlank(query.beginDate),"a.c_date",ScriptConditionEnum.Eq,query.beginDate)
                .where(StringUtils.isNotBlank(query.getExtentName()),"a.c_extent_name",ScriptConditionEnum.Eq,query.getExtentName()) ;
        sqlQueryMate.setGroupBy(" a.c_user_name,a.c_user_id,a.c_user_id_int,b.c_person_type");
        return getMap(sqlQueryMate);
    }

}
